<?php
$city = json_decode(file_get_contents('../city.json'));

$table_name = 'citys';
$sql_file = '../sql/citys.sqlite.sql';
$time = date('Y-m-d H:i:s');
$top = <<<EOF
/*
 Server Type : SQLite
 Date : $time
*/

PRAGMA foreign_keys = false;

DROP TABLE IF EXISTS "$table_name";
CREATE TABLE "$table_name" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL DEFAULT '',
  "pid" INTEGER NOT NULL
);

\n
EOF;
file_put_contents($sql_file,$top);

$i = 1;
$val = [];
foreach($city as $v){
    $val[] = "({$v->id},'{$v->name}',{$v->pid})";
    if($i === 20){
        $i = 1;
        $sql = 'INSERT INTO "'.$table_name.'" VALUES ';
        $sql .= implode(',',$val);
        $sql .= ';'.PHP_EOL;
        $val = [];
        file_put_contents($sql_file,$sql,FILE_APPEND);
    }
    $i++;
}

$end = <<<END
\n
\n
UPDATE "sqlite_sequence" SET seq = 5054 WHERE name = '$table_name';

CREATE INDEX "name"
ON "$table_name" (
  "name" ASC
);
CREATE INDEX "pid"
ON "$table_name" (
  "pid" ASC
);

PRAGMA foreign_keys = true;
END;

file_put_contents($sql_file,$end,FILE_APPEND);
echo $sql_file.'生成完毕';